﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace DelonixRegia.Classes.Rooms
{
    public class RoomDBManager
    {
        private const string connectionString = "Data Source=.; Initial Catalog=DelonixRegia; Integrated Security=True;";

        public static Room getRoomByRoomNo(int roomNo)
        {
            Room r = null;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT * FROM Room WHERE roomNo=@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    r = new Room();
                    r.roomNo = (int)dr["roomNo"];
                    r.roomLevel = (int)dr["roomLevel"];
                    r.roomType = (int)dr["roomType"];
                    r.smoking = (Boolean)dr["smoking"];
                    r.balcony = (Boolean)dr["balcony"];
                    r.roomRate = Convert.ToDecimal(dr["roomRate"]);
                    r.roomStatus = (string)dr["roomStatus"];
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return r;
        }

        public static IEnumerable<Room> getAvailableRoom(SqlCommand comm)
        {
            ArrayList list = new ArrayList();

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                comm.Connection = conn;

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Room r = new Room();
                    r.roomNo = (int)dr["roomNo"];
                    r.roomLevel = (int)dr["roomLevel"];
                    r.roomType = (int)dr["roomType"];
                    r.smoking = (Boolean)dr["smoking"];
                    r.balcony = (Boolean)dr["balcony"];
                    r.roomRate = Convert.ToDecimal(dr["roomRate"]);
                    r.roomStatus = (string)dr["roomStatus"];
                    list.Add(r);
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return list.Cast<Room>();
        }

        public static RoomType getRoomType(int roomType)
        {
            RoomType t = null;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT * FROM RoomType WHERE roomTypeID=@roomTypeID";
                comm.Parameters.AddWithValue("@roomTypeID", roomType);
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    t = new RoomType();
                    t.roomTypeID = roomType;
                    t.name = (string)dr["name"];
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return t;
        }


    }
}